<?php
/** Customer or user class for model
*
* This is customer class model to access DB and validate the login information
*
* @author  webMAPZ
* @version 39
* @copyright copyright (c) 2011, Boston Univeristy Under BSD
* 
*/
class Model_user extends CI_Model {
    
 /** Authenticate a User
*
*  This function will validate user access with the DB
*  
*  @param varchar $username This the username to be validated
*  @param varchar $password This is the hashed password to be validated
*  @return binary if the return value is FALSE then this username and password are not correct but if it is TRUE that mean the password and username are correct
*
*  
*/
    
    function model_authenticate($username, $password)
    {
        
        // The DB query to search for the input username and password
        $query_str = "SELECT cus_id , cus_email, cus_username FROM `T_CUSTOMERS` WHERE cus_username = ? and cus_password = ?";
        
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, array($username, $password));
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        
        if ($result->num_rows() == 1)
        {
            $myarray = array(
                'type' => 1,
                'cus_id' => $result->row(0)->cus_id,
                'cus_email' => $result->row(0)->cus_email,
                'cus_username' => $result->row(0)->cus_username
            );
            return //$result->row(0)->cus_id;
            //$result->result_array();
            $myarray;
        }
        else
        {
            // The DB query to search for the input username and password
            $query_str2 = "SELECT emp_id , emp_email, emp_username FROM `T_EMPLOYEES` WHERE 
                emp_username = ? and emp_password = ?";

            // run the query with the username and password as inputs and the result will be the search criteria
            $result2 = $this->db->query($query_str2, array($username, $password));

            // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
             if ($result2->num_rows() == 1)
            {
                $myarray = array(
                    'type' => 2,
                    'emp_id' => $result2->row(0)->emp_id,
                    'emp_email' => $result2->row(0)->emp_email,
                    'emp_username' => $result2->row(0)->emp_username
                );
                return //$result->row(0)->cus_id;
                //$result->result_array();
                $myarray;
            }
            else
            {
                return false;
            }
        }
    }
    
    function check_user($cus_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT cus_id, (select E.access FROM `T_CUSTOMERS` C, `T_EMPLOYEES` E WHERE 
            C.emp_id = E.emp_id and cus_id = ?) access FROM `T_CUSTOMERS` WHERE cus_id = ?";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, array($cus_id, $cus_id));
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            //return ($result->row(0)->emp_id);
            $myarray = array(
                'cus_id' => $result->row(0)->cus_id,
                'access' => $result->row(0)->access
            );
            return //$result->row(0)->cus_id;
            //$result->result_array();
            array ($myarray);
        }
        else
        {
            return false;
        }
    }
    
    
     function check_emp($emp_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT emp_id, access FROM `T_EMPLOYEES` WHERE 
            emp_id = ? ";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str,  $emp_id);
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            //return ($result->row(0)->emp_id);
            $myarray = array(
                'emp_id' => $result->row(0)->emp_id,
                'access' => $result->row(0)->access
            );
            return //$result->row(0)->cus_id;
            //$result->result_array();
            array ($myarray);
        }
        else
        {
            return false;
        }
    }
    
    function get_member_info($cus_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT cus_fname, cus_lname, cus_email, cus_phone, cus_company, cus_billing_address, cus_shipping_address, cus_dob,
                    cp.cc_type, CP.cc_num, CP.cc_date_year, CP.cc_date_month, CP.cc_3digit, CP.paypal_acc , CO.`name`
                    FROM `T_CUSTOMERS` C, `t_countries` CO, `T_CUS_PAYMENT` CP WHERE C.c_id = CO.c_id AND C.cus_pay_id = CP.payment_id
                    and c.cus_id = ?";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, $cus_id);
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            $cus_billing_address_string = $this->encrypt->decode($result->row(0)->cus_billing_address);
            $cus_shipping_address_string = $this->encrypt->decode($result->row(0)->cus_shipping_address);
            $cc_num_string = $this->encrypt->decode($result->row(0)->cc_num);
            $cc_3digit_string = $this->encrypt->decode($result->row(0)->cc_3digit);
            
            $result->row(0)->cus_billing_address = $cus_billing_address_string;
            $result->row(0)->cus_shipping_address = $cus_shipping_address_string;
            $result->row(0)->cc_3digit = $cc_3digit_string;
            $result->row(0)->cc_num = $cc_num_string;  
           
           
            return $result->result();
        }
        else
        {
            return array();
        }
    }
    
    
    function get_employee_only_info($emp_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT emp_firstname, emp_lastname, emp_email, address, emp_dob, CO.`name`
                    FROM `T_EMPLOYEES` E, `T_countries` CO WHERE E.c_id = CO.c_id 
                    and E.emp_id = ?";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, $emp_id);
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            
            $address_string = $this->encrypt->decode($result->row(0)->address);
            
            $result->row(0)->address = $address_string;
            
            return //$result->row(0)->cus_id;
            //$result->result_array();
             $result->result();
            //array ($myarray);
            //$result;
        }
        else
        {
            return array();
            //false;
        }
    }
    
     function get_employee_info($cus_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT cus_fname, cus_lname, cus_email, cus_phone, cus_company, cus_billing_address, cus_shipping_address, cus_dob,
                    cp.cc_type, CP.cc_num, CP.cc_date_year, CP.cc_date_month, CP.cc_3digit, CP.paypal_acc , CO.`name`
                    FROM `T_CUSTOMERS` C, `t_countries` CO, `T_CUS_PAYMENT` CP WHERE C.c_id = CO.c_id AND C.cus_pay_id = CP.payment_id
                    and c.cus_id = ?";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, $cus_id);
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            return //$result->row(0)->cus_id;
            //$result->result_array();
             $result->result();
            //array ($myarray);
            //$result;
        }
        else
        {
            return array();
            //false;
        }
    }
    
    /* function get_admin_info($cus_id)
    {
         // The DB query to search for the input username and password
        $query_str = "SELECT emp_firstname, cus_lname, cus_email, cus_phone, cus_company, cus_billing_address, cus_shipping_address, cus_dob,
                    cp.cc_type, CP.cc_num, CP.cc_date_year, CP.cc_date_month, CP.cc_3digit, CP.paypal_acc , CO.`name`
                    FROM `T_CUSTOMERS` C, `t_countries` CO, `T_CUS_PAYMENT` CP WHERE C.c_id = CO.c_id AND C.cus_pay_id = CP.payment_id
                    and c.cus_id = ?";
        // run the query with the username and password as inputs and the result will be the search criteria
        $result = $this->db->query($query_str, $cus_id);
        
        // check if the result contains at one row of the DB query it will return the customer id  or will return FALSE
        if ($result->num_rows() == 1)
        {
            return //$result->row(0)->cus_id;
            //$result->result_array();
             $result->result();
            //array ($myarray);
            //$result;
        }
        else
        {
            return array();
            //false;
        }
    }*/
    
    function model_update_user($cus_id,$first_name, $last_name, $dob, $email, $shipaddress, 
            $billaddress, $countryname, $credit_card_type, $credit_card_number, $credit_card_month, 
            $credit_card_year, $credit_card_cvv, $paypal)//, $movies_membership, $games_membership)
    {
       // The DB query to search for a specific username in the DB 
        // $query_str = "SELECT emp_id FROM T_EMPLOYEES WHERE EMP_EMAIL = ?";
         // run the query with $username info to get the results of users with the same username
        // $result = $this->db->query($query_str, $email);

         // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
         //if ($result->num_rows() == 1)
         //   {
                
         //$query_str = "CALL UPDATE_CUSTOMER(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                // The DB query to insert the new cusotomer information in the DB 
               // $query_str = "INSERT INTO `T_CUSTOMER` (cus_username, cus_fname, cus_lname, cus_email, cus_password, cus_address, zipcode) VALUES ( ?, ?, ?, ?, ?, ?, ?)";
        $query_str = "SELECT cus_pay_id FROM `T_CUSTOMERS` WHERE cus_id = ? ";
        $result =  $this->db->query($query_str, $cus_id);
        
        $pay = $result->row(0)->cus_pay_id;
        
	$query_str = "SELECT c_id FROM `T_COUNTRIES` WHERE name = ? ";
        $result_co =  $this->db->query($query_str, $countryname);
	
        $country = $result_co->row(0)->c_id;
        
        $result_run = "UPDATE `T_CUS_PAYMENT` SET cc_type = ? , cc_num = ? , cc_date_month = ? , 
		cc_date_year = ?, cc_3digit = ? , paypal_acc = ? WHERE payment_id = ? ";
        $result_run1 =  $this->db->query($result_run, array($credit_card_type, $credit_card_number, 
            $credit_card_month, $credit_card_year, $credit_card_cvv,$paypal,$pay));	
	$result_run = "UPDATE `T_CUSTOMERS` SET cus_fname = ? , cus_lname = ? , cus_email = ? ,
		cus_billing_address = ? , cus_shipping_address = ? ,
                c_id = ? , cus_dob = ? WHERE cus_id = ? " ;
        $result_run2 =  $this->db->query($result_run, array($first_name, $last_name, 
            $email, $billaddress, $shipaddress, $country, $dob, $cus_id));     
               
             if ($result_run2 && $result_run1)
                {
                // sending an confirmation email for the registration
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Confirmation from YOGETTE');
                    $this->email->message('This is a Confirmation, Your Information has been updated in Yogette. Congratulations, Thank you and have anice day');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return FALSE;
                }
    }
    
    
    
    function model_update_employee($emp_id,$first_name, $last_name, $dob, $email, $address, 
             $countryname)
    {
        
	$query_str = "SELECT c_id FROM `T_COUNTRIES` WHERE name = ? ";
        $result_co =  $this->db->query($query_str, $countryname);
	
        $country = $result_co->row(0)->c_id;
        
       
	$result_run = "UPDATE `T_EMPLOYEES` SET emp_firstname = ? , emp_lastname = ? , emp_email = ? ,
		address = ? , c_id = ? , emp_dob = ? WHERE emp_id = ? " ;
        $result_run1 =  $this->db->query($result_run, array($first_name, $last_name, 
            $email, $address, $country, $dob, $emp_id));     
              
             if ($result_run1)
                {
                // sending an confirmation email for the registration
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Confirmation from YOGETTE');
                    $this->email->message('This is a Confirmation, Your Employment Information has been updated in Yogette. Congratulations, Thank you and have anice day');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return FALSE;
                }
    }
    
    function check_exist_paypal($cus_id , $paypal)
    {
     $query_str = "SELECT paypal_acc FROM `T_CUS_PAYMENT`, `T_CUSTOMERS` where 
         cus_pay_id = payment_id and cus_id = ?";
     
     $result = $this->db->query($query_str, $cus_id);
     // The DB query to search for a specific email in the DB
     $cus_paypal = $result->row(0)->paypal_acc;
     
     $query_str = "SELECT paypal_acc FROM `T_CUS_PAYMENT` where paypal_acc = ?";
     $result2 = $this->db->query($query_str, $paypal);
     
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result2->num_rows() > 0 && $result->num_rows() > 0)
        {
          $new_paypal_acc = $result2->row(0)->paypal_acc;
         if ($cus_paypal != $new_paypal_acc)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
    }
     function check_exist_credit($cus_id , $credit_card_number)
    {
     
        $query_str = "SELECT cc_num FROM `T_CUS_PAYMENT`, `T_CUSTOMERS` where 
        cus_pay_id = payment_id and cus_id = ?";
     
        $result = $this->db->query($query_str, $cus_id);
        // The DB query to search for a specific email in the DB
        $cus_cc_num = $result->row(0)->cc_num;
     
        $query_str2 = "SELECT cc_num FROM `T_CUS_PAYMENT` where cc_num = ?";
     
         // run the query with $email info to get the results of users with the same email
         $result2 = $this->db->query($query_str2, $credit_card_number);
     
         
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result2->num_rows() > 0 && $result->num_rows() > 0)
        {
         $new_num_acc = $result2->row(0)->cc_num;
         if ($cus_cc_num != $new_num_acc)
             {
                return true;
            }
            else
            {
                return false;
            }
         
        }
        else
        {
            return false;
        }
    }
    
    function add_libility($cus_id , $total)
        {
            $query_str = "SELECT cus_pay_id FROM `T_CUSTOMERS` where 
             cus_id = ?";

            $result = $this->db->query($query_str, $cus_id);
            // The DB query to search for a specific email in the DB
            $cus_pay_id = $result->row(0)->cus_pay_id;

            $query_str2 = "UPDATE `T_CUS_PAYMENT` SET dues = dues + ? WHERE payment_id = ?";

             // run the query with $email info to get the results of users with the same email
             $result2 = $this->db->query($query_str2, array($total,$cus_pay_id));
            
             if ($result && $result2)
                {
                    return true;
                }
                else
                {
                    return false;
                }
        }
        function paid_cart($cus_id , $email, $total , $res_queue)
        {
            $query_str = "SELECT cus_pay_id FROM `T_CUSTOMERS` where 
             cus_id = ?";

            $result = $this->db->query($query_str, $cus_id);
            // The DB query to search for a specific email in the DB
            $cus_pay_id = $result->row(0)->cus_pay_id;

            $query_str2 = "UPDATE `T_CUS_PAYMENT` SET dues = dues + ? WHERE payment_id = ?";

             // run the query with $email info to get the results of users with the same email
             $result2 = $this->db->query($query_str2, array($total,$cus_pay_id));
            
             foreach ($res_queue['items'] as $item) 
              { 
                 $query_str3 = "UPDATE `T_CUS_CART` SET order_purch_flag = 1, shipped_on = now()
                     , status = 'Shipped' WHERE order_id = ?";
                 $result3 = $this->db->query($query_str3, $item->order_id);
                 
                 $query_str4 = "INSERT INTO `T_CUS_CART_HIST` (order_id , cus_id, 
                          prod_id , store_loc_id , special_flag , special_desc , order_date,
                          order_purch_flag , status  , shipped_on , shipping_notes, shipping_method,
                          payment_date) 
                          VALUES (?, ?, ?, ?, ?, ?, ?, 1, 'Shipped', now(), ? ,? , now())";
                 
                 $result4 = $this->db->query($query_str4, array($item->order_id,
                           $item->cus_id,$item->prod_id,$item->store_loc_id,$item->special_flag,
                           $item->special_desc,$item->order_date,
                           $item->shipping_notes,$item->shipping_method));
              }
         // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
         if ($result && $result2 && $result3 && $result4)
                {
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Purchase Confirmation from YOGETTE');
                    $this->email->message('This is a Confirmation, Your Items will be shipped Shortly, Your credit Card Has been Charged the Total Amount Of:
                        '.$total.'$. Thank you for Shopping With Us Today.');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return false;
                }
            
        } 
        
        function add_libility_from_emp($emp_id , $email , $res_queue)
        {
            $query_str = "SELECT cus_pay_id FROM `T_CUSTOMERS` where 
             cus_id = ?";
            foreach ($res_queue['items'] as $item) 
            { 
            $result = $this->db->query($query_str, $item->cus_id);
            // The DB query to search for a specific email in the DB
            
            $total = $item->p_price + $item->p_tax;
            $cus_pay_id = $result->row(0)->cus_pay_id;

            $query_str2 = "UPDATE `T_CUS_PAYMENT` SET dues = dues + ? WHERE payment_id = ?";

             // run the query with $email info to get the results of users with the same email
             $result2 = $this->db->query($query_str2, array($total,$cus_pay_id));
            
             
             $query_str3 = "UPDATE `T_CUS_CART` SET order_purch_flag = 1, shipped_on = now()
                 , status = 'Shipped' WHERE order_id = ?";
             $result3 = $this->db->query($query_str3, $item->order_id);
                 
             $query_str4 = "INSERT INTO `T_CUS_CART_HIST` (order_id , cus_id, 
                      prod_id , store_loc_id , special_flag , special_desc , order_date,
                      order_purch_flag , status  , shipped_on , shipping_notes, shipping_method,
                      payment_date) 
                      VALUES (?, ?, ?, ?, ?, ?, ?, 1, 'Shipped', now(), ? ,? , now())";
                 
                 $result4 = $this->db->query($query_str4, array($item->order_id,
                           $item->cus_id,$item->prod_id,$item->store_loc_id,$item->special_flag,
                           $item->special_desc,$item->order_date,
                           $item->shipping_notes,$item->shipping_method));
              }
         // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
         if ($result && $result2 && $result3 && $result4)
                {
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Purchase Confirmation from YOGETTE');
                    $this->email->message('This is a Confirmation, Your Items will be shipped Shortly, Your credit Card Has been Charged the Total Amount Of:
                        '.$total.'$. Thank you for Shopping With Us Today.');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return false;
                }
            
        } 
        
     function block_customer($cus_id)
        {
         
            $query_str = "SELECT cus_email FROM `T_CUSTOMERS` where 
             cus_id = ?";
           
            $result = $this->db->query($query_str, $cus_id);
            
            $cus_email = $result->row(0)->cus_email;

            $query_str2 = "UPDATE `T_SPE_RESERV_HIS` SET black_listed_date = now() WHERE cus_id = ?";
            
            $result2 = $this->db->query($query_str2, $cus_id);
            
            if ($result && $result2)
                {
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($cus_email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Block Notice from YOGETTE');
                    $this->email->message('Sorry Your have been Blocked for Future Reservation, Since You had "No Show Up" Case. Please Contact Us If you see this is happend by mistake or error.');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return false;
                }
         
        }
        
        function un_block_customer($cus_id)
        {
         
            $query_str = "SELECT cus_email FROM `T_CUSTOMERS` where 
             cus_id = ?";
           
            $result = $this->db->query($query_str, $cus_id);
            
            $cus_email = $result->row(0)->cus_email;

            $query_str2 = "UPDATE `T_SPE_RESERV_HIS` SET black_listed_date = null WHERE cus_id = ?";
            
            $result2 = $this->db->query($query_str2, $cus_id);
            
            if ($result && $result2)
                {
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($cus_email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Unblocked Account Confirmation from YOGETTE');
                    $this->email->message('Congratulation your Account has been Unblocked , You may Start Booking and Reserving Special Event in our Branches. Thank you For choosing Yoggete.');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return false;
                }
         
        }
            
}


?>